計算式の応用
VBA関数とワークシート関数
「関数」とは、値を返す命令のことをいい、関数の実行により得られた値のことを「戻り値」といいます。関数にはVBA関数とワークシート関数があります。
VBA関数は、ステートメント中で計算結果の戻り値を変数に代入したり、MsgBoxに表示したり、あるいは条件式などに利用します。戻り値としての数値や文字列をセルに設定することもできます。 ワークシート関数は、Excelのワークシート上で使用される関数ですから、セルに計算式として設定することができます。ステートメントの中でVBA関数のように戻り値を得るために利用できるワークシート関数は、ヘルプの「Visual
Basic
で使用できるワークシート関数一覧」で確認してください。
ワークシート関数は、使用方法によって記述の仕方が異なります。
例1： セルに計算式として入力するとき（セル A10
に計算式を入力）
Range("A10").Formula = "=SUM(A1:A9)"
例2： 戻り値を得るために使用するとき(セル A10
に計算結果を入力)
Range("A10").Value =
Application.WorksheetFunction.Sum(Range("A1:A9"))
処理内容：システム日付の「日」の戻り値を変数に代入します。
Sub function_1()
Dim Hi As Date
Hi = Day(Date)
Range("A1") = Hi
End Sub
処理内容：システム日付の「日」の戻り値をセルA1に入力します。
Sub function_2()
Worksheets("Sheet1").Range("A1") =
Day(Date)
End Sub
処理内容：システム日付の「日」の戻り値をMsgBoxに表示します。
Sub function_3()
MsgBox Day(Date)
End Sub
処理内容：システム日付の「日」の戻り値を条件式として「旬」をMsgBoxに表示します。
Sub function_4()
Select Case Day(Date)
Case 1 to 10:MsgBox "上旬"
Case 11 to 20:MsgBox "中旬"
Case 21 to 31:MsgBox "下旬"
End Select
End Sub
処理内容：セルA10に、A1からA9までの平均を求める計算式を入力します。
Sub function_5()
Range("A10").Formula = "=AVERAGE(A1:A9)"
End Sub
処理内容：セルA10に、A1からA9までの平均を求めた計算結果を入力します。。
Sub function_6()
Range("A10").Value = Application.WorksheetFunction.Average(Range("A1:A9"))
End Sub
処理内容：tCodeの値を検索値として、Choose関数により都市名に変換した結果を求めます。
Sub function_7()
tCode = 2
tName = Application.WorksheetFunction.Choose(tCode, "東京", "名古屋", "大阪")
MsgBox tName
End Sub
処理内容：セルA1を検索値として、セルB1にChoose関数の計算式を入力します。
Sub function_8()
Range("A1").Value = 2
Range("B1").Formula = "=Choose(A1,""東京"",""名古屋"",""大阪"")"
End Sub
計算式中で文字列を扱うときは、「 " (ダブルクオーテーション)」で囲まなければなりませんが、計算式を入力するときは、その外側も「 "
」で囲む必要があります。
配列数式の使用法
VBAで計算結果だけを求める場合は、配列数式を使用する必要はないかもしれませんが、複数の条件を満たす計算式をセルに入力する場合などには配列数式が便利なこともあります。配列数式の詳細についてはここをクリックしてください。
配列数式を入力するときには、FormulaArray
プロパティを使用します。
処理内容：リスト範囲「A1:C12」、A列が「5」かつ、B列が「A」に該当するC列の数値を合計します。
Sub Array_1()
Range("C13").FormulaArray = "=SUM((A1:A12=5)*(B1:B12=""A"")*C1:C12)"
End Sub
処理内容：リスト範囲「A1:C12」、A列が「5」かつ、B列が「A」に該当する件数を合計します。
Sub Array_2()
Range("B13").FormulaArray = "=SUM((A1:A12=5)*(B1:B12=""A""))"
End Sub
串刺し計算(統合)
串刺し計算(統合)の方法には、「位置による統合」「項目による統合」の2種類があります。
この機能の詳細についてはここをクリックしてください。
VBAでの統合の書式とそれぞれの引数の意味は下記のとおりです。
expression.Consolidate(Sources, Function, TopRow, LeftColumn, CreateLinks)
なお、expression
は、統合先データ範囲の左上端セルを指定します。(位置による統合の場合はデータ部分の左上端とします。)
|
引 数
|
指 定 内 容
|
| Sources |
統合元範囲を参照する、R1C1
形式の文字列を指定します。指定するセル参照には、必ず統合するシートのシート名とセル範囲を含めます。
位置による統合のときは、データ範囲(見出し部分を除く。)を指定します。
項目による統合のときは、見出し部分を含めた範囲を指定します。 |
| Function |
集計方法を指定します。
xlAverage、xlCount、xlCountNums、xlMax、xlMin、xlProduct、xlStDev、xlStDevP、xlSum、xlVar、xlVarP のいずれかです。既定値は xlAverage |
| TopRow |
統合する範囲の上端行に入力されている列見出しを基準として統合する場合は、True を指定します。データの位置に基づく場合は、False を指定します。既定値は False です。 |
| LeftColumn |
統合する範囲の左端列に入力されている行見出しを基準として統合する場合は、True を指定します。データの位置に基づく場合は、False を指定します。既定値は False です。 |
| CreateLinks |
ワークシート リンクを使う場合は、True
を指定します。データをコピーする(リンクを行わない)場合は、False
を指定します。既定値は False です。 |
処理内容：リスト範囲「A1:D6」、上端行、左端列は見出しである表の位置による統合。リンクはなし。
Sub Consolidate_1()
Worksheets("Sheet3").Range("B2").Consolidate _
Sources:=Array("Sheet1!R2C2:R6C4", "Sheet2!R2C2:R6C4"), _
Function:=xlSum
End Sub
処理内容：リスト範囲「A1:D6」、上端行、左端列は見出しである表の項目による統合。リンクする。
Sub Consolidate_2()
Worksheets("Sheet3").Range("A1").Consolidate _
Sources:=Array("[Book2]Sheet1!R1C1:R6C4", "[Book2]Sheet2!R1C1:R6C4"), _
Function:=xlSum, TopRow:=True, LeftColumn:=True,
CreateLinks:=True
End Sub
ピボットテーブル
ピボットテーブルの作成、編集を当初からVBAで記述しようとすると非常に面倒な作業となります。マクロの自動記録機能を有効に活用して、記録されたコードを編集するのも一方法です。ピボットテーブルの詳細については、ここをクリックして下さい。
下記は、マクロの自動記録で作成されたコードを編集したものです。
処理内容：Excelシートの表から品名・単価別の金額を求めます。
Sub Pivot_1()
Worksheets("Sheet1").Select
Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:="Sheet1!R1C1:R6C4"). _
CreatePivotTable TableDestination:=Range("F1"), _
TableName:="ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1"
With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1")
.SmallGrid = False
.AddFields RowFields:="品名", ColumnFields:="単価"
.PivotFields("金額").Orientation = xlDataField
End With
End Sub
期間・時間計算
Excel Q & A コーナーや BBS などで期間計算や時間計算に関する質問をよく見かけますので、計算式例を紹介します。
処理内容：セルA2に始期の年月日、B2に終期の年月日を入力して期間計算を行います。
Sub Pivot_1()
Worksheets("Sheet1").Select
Range("A1").Value = "始期"
Range("B1").Value = "終期"
Range("A2").Value = "1985/10/5"
Range("B2").Value = Date
Range("A3").Value = "1.期間内の年数"
Range("B3").Formula ="=DATEDIF($A$2,$B$2,""Y"")"
Range("A4").Value = "2.期間内の月数"
Range("B4").Formula ="=DATEDIF($A$2,$B$2,""M"")"
Range("A5").Value = "3.期間内の日数"
Range("B5").Formula ="=DATEDIF($A$2,$B$2,""D"")"
Range("A6").Value = "4.期間内の1年未満の月数"
Range("B6").Formula ="=DATEDIF($A$2,$B$2,""YM"")"
Range("A7").Value = "5.期間内の1ヶ月未満の日数"
Range("B7").Formula ="=DATEDIF($A$2,$B$2,""MD"")"
Range("A8").Value = "6.期間内の1年未満の日数"
Range("B8").Formula ="=DATEDIF($A$2,$B$2,""YD"")"
End Sub
処理内容：Day,Eomonth関数によりシステム日付の最終日を求めます。
Sub Pivot_1()
Worksheets("Sheet1").Select
Range("A1").Formula = "=Day(Eomonth(Today(), 0))"
End Sub
処理内容：DateAdd関数により指定した翌月の最終日付を求めます。
Sub Pivot_1()
Dstart = "2001/5/1"
Dend = DateAdd("m", 2, Dstart) - 1
MsgBox Dend
End Sub
処理内容：Day関数によりシステム日付の前月の最終日付を求めます。
Sub Pivot_1()
MsgBox "先月末は" & (Date - Day(Date)) & "です。"
End Sub
処理内容：開始時間から終了時間までの経過時間を求めます。
Sub time_1()
Worksheets("Sheet1").Select
Range("A1:C1").NumberFormatLocal = "[h]:mm"
Range("A1").Value="12:15"
Range("B1").Value="16:45"
Range("C1").Formula = "=B1-A1"
End Sub
処理内容：経過時間の合計を求めます。
Sub time_2()
Worksheets("Sheet1").Select
Range("A1:C3").NumberFormatLocal = "[h]:mm"
Range("A1").Value="0:15"
Range("B1").Value="16:45"
Range("A2").Value="14:30"
Range("B2").Value="24:15"
Range("C1:C2").Formula = "=B1-A1"
Range("C3").Formula = "=SUM(C1:C2)"
End Sub
処理内容：時間の分の部分を10進法で表示します。
Sub time_3()
Worksheets("Sheet1").Select
Range("A1").Value = "10:15"
Range("A2").Formula = "=A1 * 24" '
結果は、「10.25」となる
Range("A3").Formula = "=A1 / TimeValue(""1:00:00"")" '
結果は、「10.25」となる
Range("A2:A3").NumberFormatLocal = "G/標準"
End Sub
処理内容：時間の「分」の部分を60進法(時間表示)で表示します。
Sub time_4()
Worksheets("Sheet1").Select
Range("A1").Value = 10.25
Range("A2").Formula = "=A1/24" '
結果は、「10:15」となる
Range("A3").Formula = "=A1* TimeValue(""1:00:00"")" '
結果は、「10:15」となる
Range("A2:A3").NumberFormatLocal = "h:mm"
End Sub
処理内容：時間の切り上げ、切り捨て
Sub time_5()
Worksheets("Sheet1").Select
Range("A1").Value = "処理内容"
Range("A2").Value = "15分単位で切り上げ"
Range("A3").Value = "15分単位で切り捨て"
Range("A4").Value = "30分以下切り捨て"
Range("A5").Value = "30分以上切り上げ"
Range("B1").Value = "15:20" '
テストデータ
Range("C1").Value = "15:30" '
テストデータ
Range("D1").Value = "15:40" '
テストデータ
Range("B2:D2").Formula = "=CEILING(B1,""0:15"")" '
15分単位で切り上げ
Range("B3:D3").Formula = "=FLOOR(B1,""0:15"")" '
15分単位で切り捨て
Range("B2:D3").NumberFormatLocal = "h:mm" '
書式設定
Range("B4:D4").Formula = "=ROUNDUP(B1*24-0.5,0)" '
30分以下切り捨て
Range("B5:D5").Formula = "=ROUNDDOWN(B1*24+0.5,0)" '
30分以下切り上げ
Range("B4:D5").NumberFormatLocal = "#,##0.00" '
書式設定
Cells.Columns.AutoFit '
列幅自動調整
End Sub
フィルタ実行後の処理
オートフィルタやフィルタオプションでデータを抽出した後の処理を、思いつくまま拾ってみました。
下記は、Excel Q & A
などでもよく質問がある内容だと思います。他の項のコードと重複するものがありますが、悪しからず。
処理内容：B列に基づく抽出データのみ、A列にナンバリングします。(A列には見出し有り)
Sub filta_1()
Dim Num As Long,lRow As Long
Num = 1
Range("B2").AutoFilter Field:=2, Criteria1:="りんご"
lRow = Range("B2").CurrentRegion.SpecialCells(xlLastCell).Row
Range(Cells(2, 2), Cells(lRow, 2)).SpecialCells(xlVisible).Select
For Each Cel In Selection
Cel.Offset(, -1).Value = Num
Num = Num + 1
Next
Range("B2").AutoFilter
End Sub
処理内容：数値データが3列目に入力されているものとして合計します。（１行目は見出し）
Sub filta_2()
Dim Total As Long
Total = 0
Range("A1").AutoFilter Field:=2, Criteria1:="りんご"
Range("A1").CurrentRegion.Select
Selection.Offset(1).Resize(Selection.Rows.Count - 1).Select
Selection.Columns(3).SpecialCells(xlVisible).Select
For Each cel In Selection
Total = Total + cel.Value
Next
Range("A1").AutoFilter
MsgBox Total
End Sub
|
3 オートフィルタで抽出したデータの数をカウントする |
処理内容：オートフィルタで抽出したデータ件数をメッセージボックスに表示します。
Sub filta_3()
Range("A1").AutoFilter Field:=2, Criteria1:="りんご"
MsgBox Range(Range("A2"), Cells(Rows.Count, 1).End(xlUp)). _
SpecialCells(xlCellTypeVisible).Count
Range("A1").AutoFilter
End Sub
処理内容：オートフィルタの抽出結果データを、同一シートのE1を左上端として複写します。
Sub filta_4()
Dim CopyArea As Range
Range("A1").AutoFilter Field:=2, Criteria1:="りんご"
Set CopyArea = Range("A1").CurrentRegion
CopyArea.SpecialCells(xlCellTypeVisible).Copy
Range("E1").PasteSpecial
Range("A1").AutoFilter
End Sub
計算式の小技
Excel
の操作でチョット便利かなと思う小技集を紹介します。
処理内容：文字列で入力された計算式の結果を表示します。
Sub test_1()
Range("A1").Value = "5*2"
Range("B1").Value = Evaluate(Range("A1").Value)
End Sub
処理内容：セルA1:A10に1から始まる連番を入力します。
Sub test_2()
Range("A1") = 1
Range("A1:A10").DataSeries Rowcol:=xlColumns, Step:=1
End Sub
処理内容：指定した文字(C)から右側の文字列を別のセルに転記します。
Sub test_3()
Cells(1,1).value="ABCDEF"
intA = InStr(Cells(1, 1).Value, "C")
Cells(2, 1).Value = Trim(Mid$(Cells(1, 1), intA + 1))
End Sub
処理内容：小数点の有効桁数を指定して四捨五入を行い、表示書式も指定桁数に揃えます。
Sub test_4()
Dim K As Integer, zero As String
Range("A1").Value=123.45678
K = InputBox("小数点以下の有効桁数を入力")
zero = Application.WorksheetFunction.Rept("0", K)
Range("B1").Formula = "=ROUND(A1," & K & ")"
Range("B1").NumberFormatLocal = "0." & zero
End Sub
処理内容：別シートのコードリストを参照して、コートナンバーから氏名に一括変換します。
Sub test_5()
Dim K As Integer, zero As String
R1 = Cells(Sheets("コードリスト").Rows.Count, 1).End(xlUp).Row
R2 = Cells(Sheets("データリスト").Rows.Count, 1).End(xlUp).Row
list1 = "コードリスト!$A$1:$B$" & R1 '
コードリスト範囲
list2 = "B1:B" & R2 ' データリスト氏名表示先
Sheets("データリスト").Range(list2).Formula = _
"=IF(A1="""","""",VLOOKUP(A1," & list1 & ",2,0))"
End Sub
処理内容：計算式が入力されたセルの右に計算式を表示します。
Sub test_6()
Range("A1").Formula = "=LEN(""ABCDE"")"
Range("B1") = "'" & Range("B1").Offset(, -1).Formula
End Sub